package DB;

import Model.Customer;
import Model.SalesOrder;
import Model.SalesOrderProduct;

import java.sql.*;
import java.util.ArrayList;

public class SalesOrderDB implements SalesOrderDBIF {
    private Connection conn;
    private PreparedStatement prepStatement;
    private SalesOrderProductDB salesOrderProductDB;

    public SalesOrderDB(){
        conn = ConnectionDB.getInstance().getConn();
        salesOrderProductDB = new SalesOrderProductDB();
    }

    public SalesOrder findSalesOrder(int id) {
        SalesOrder salesOrder;
        salesOrder = findWithPreQuery(id);
        return salesOrder;
    }

    public ArrayList<SalesOrder> getAllSalesOrders(){
        return miscWhere("", false);
    }

    @Override
    public int insertSalesOrder(SalesOrder salesOrder) {
        int rc = -1;
        PreparedStatement pstmt;

        String insert = "INSERT INTO SalesOrder (id, date, amount, deliveryStatus, deliveryDato, " +
                "customer) " + "VALUES ("+
                salesOrder.getId()  + ",'"  +
                salesOrder.getDate()  + "',"  +
                salesOrder.getTotalPrice() + ",'" +
                salesOrder.getDeliveryStatus()  + "','"  +
                salesOrder.getDeliveryDate()  + "',"  +
                salesOrder.getCustomer()+")";

        System.out.println(insert);
        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(insert  );
            stmt.close();
            insertSalesOrderProduct(salesOrder);
        }
        catch (SQLException ex){
            System.out.println("Insert error: " + ex);
        }

        return rc;
    }

    @Override
    public int deleteSalesOrder(int id) {
        int rc = -1;

        String query = "DELETE FROM SalesOrder WHERE id = '" + id + "'";

        System.out.println(query);

        try {
            deleteSalesOrderProduct(id);
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (SQLException e){
            System.out.println("Delete exception in salesOrder db: "+ e);
        }


        return rc;

    }

    private void deleteSalesOrderProduct(int salesOrderID){
        salesOrderProductDB.deleteSalesOrderProduct(salesOrderID);

    }

    private void insertSalesOrderProduct(SalesOrder salesOrder){

        for (SalesOrderProduct s: salesOrder.getSalesOrderProductList()){
             salesOrderProductDB.insertSalesOrderProduct(s);
        }
    }



    private PreparedStatement getPreparedStmt()
    {
        try{
            String query = "select id, date, amount, deliveryStatus, deliveryDato, customer from salesorder" +
                    " where id = ?";
            System.out.println("Prepared statement: " + query);
            prepStatement = conn.prepareStatement(query);

        }
        catch(Exception e){
            e.getMessage();
        }
        return prepStatement;
    }

    private SalesOrder singleWhere(String wClause)
    {
        ResultSet results;
        SalesOrder salesOrder = new SalesOrder();

        String query =  buildQuery(wClause);
        System.out.println(query);
        try{
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next() ){
                salesOrder = buildSalesOrder(results);
                salesOrder.setSalesOrderProductList(getSalesOrderProducts(salesOrder));
                stmt.close();
            }
        }
        catch(Exception e){
            System.out.println("Query exception: "+e);
        }
        return salesOrder;
    }

    private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();

        String query =  buildQuery(wClause);
        System.out.println("SalesOrderDB " + query);
        try{ // read from project
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while( results.next() ){
                SalesOrder salesOrderObj = new SalesOrder();
                salesOrderObj = buildSalesOrder(results);
                list.add(salesOrderObj);
            }

            stmt.close();
        }
        catch(Exception e){
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }

    private ArrayList<SalesOrderProduct> getSalesOrderProducts(SalesOrder s){
        return salesOrderProductDB.getSalesOrderProduct(s);
    }


    private SalesOrder buildSalesOrder(ResultSet results)
    {
        SalesOrder salesOrder = new SalesOrder();
        try {
            salesOrder.setId(results.getInt("id"));
            salesOrder.setDate(results.getString("date"));
            salesOrder.setDeliveryStatus(results.getString("deliveryStatus"));
            salesOrder.setDeliveryDate(results.getString("deliveryDato"));
            salesOrder.setCustomer(results.getInt("customer"));
            salesOrder.setSalesOrderProductList(getSalesOrderProducts(salesOrder));
        }
        catch(SQLException ex) {
            System.out.println("Error in building salesorder: " + ex);
        }
        return salesOrder;
    }

    public SalesOrder findWithPreQuery(int id) {
        if(prepStatement == null) {
            prepStatement = getPreparedStmt();
        }

        SalesOrder salesOrder = null;
        ResultSet results;

        try {
            prepStatement.setInt(1, id);
            results = prepStatement.executeQuery();

            if(results.next()) {
                salesOrder = buildSalesOrder(results);
            }
        }
        catch (Exception e) {
            e.getMessage();
        }

        return salesOrder;
    }

    private String buildQuery(String wClause)
    {
        String query="SELECT id, date, amount, deliveryStatus, deliveryDato, customer FROM SalesOrder";

        if (wClause.length()>0)
            query = query + " where " + wClause;

        return query;
    }

    public int salesOrderGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(id) from SalesOrder");

        nextId = nextId +1;

        return nextId;
    }
}
